import xlrd,xlwt
import pandas as pd
import sys
from xlutils.copy import copy
filename = "/Users/wangshuguan/Desktop/建筑宝数据结构.xls"
sheet_names = xlrd.open_workbook(filename).sheet_names()
def readSheet(filename,sheetname="", header=1):
    try:
        sheet_names = xlrd.open_workbook(filename).sheet_names()
        if len(sheetname) == 0:
            sheet = pd.read_excel(filename, sheet_names[0], index_col=None, na_values=['NA'], header=header)
        else:
            sheet = pd.read_excel(filename, sheetname, index_col=None, na_values=['NA'], header=header)
    except IOError:
        print("无法打开文件")
        sys.exit()
    return sheet

def getUrlFromSource(name):
    df_dataSource = readSheet("/Users/wangshuguan/Desktop/数据源.xlsx")
    values = df_dataSource[df_dataSource.网站名称 == name].网址
    if len(values)>0:
        return values.values[0]
    else:
        return ""


# print(getUrlFromSource("中国建造师网1"))
# sys.exit()
def createSummary():
    olddata = readSheet(filename, "信用中国")
    df = olddata.copy()
    for x in sheet_names:
        df[x] = ""
    
    for sheetname in sheet_names:
    
        sheet = readSheet(filename, sheetname)
        sheet2 = sheet[pd.notna(sheet.对应数据)]
        sheet3 = sheet2.fillna(value='')
        sheet4 = sheet3[sheet3.对应数据 == "有"]
        for x in sheet4.index:
            df.at[x,sheetname] = "有"
            
    with pd.ExcelWriter('/Users/wangshuguan/Desktop/数据统计.xls') as writer:
        df.to_excel(writer, sheet_name='数据统计')
        
def dealHylink():
    rb = xlrd.open_workbook('/Users/wangshuguan/Desktop/数据统计.xls')
    Workbook = copy(rb)
    mySheet = Workbook.get_sheet(0)
    for i in range(0,len(sheet_names)):
        col = i+6
        name = rb.sheet_by_index(0).cell(0, col).value
        link = getUrlFromSource(name)
        mySheet.write(0, col,xlwt.Formula(f'HYPERLINK("{link}","{name}")'))
    Workbook.save('/Users/wangshuguan/Desktop/数据统计.xls')
if __name__ == "__main__":
#     createSummary()
    dealHylink()